import os
import glob
import xlrd
import xlwt

directory = 'D:\\bwq-work\\'  # 文件夹位置
file_paths = glob.glob(os.path.join(directory, '*.xlsx'))  # 获取目录下的所有 xlsx文件
ignoreFile = os.path.join(directory, '~$')  # 打开excel生成的缓存文件


class TodayData:  # 某一天的数据格式
    def __init__(self):
        self.dayList = None
        self.fileName = None


class TargetData:  # 匹配当前Excel表格是数据格式A-H列
    def __init__(self):
        self.A = None
        self.B = None
        self.C = None
        self.D = None
        self.E = None
        self.F = None
        self.G = None
        self.H = None


allData = []  # 所有天数据
for file_path in file_paths:
    if file_path.startswith(ignoreFile):
        pass
    else:
        fileName = file_path.replace(directory, '')  # 文件名,例如: 8月14日县镇村完成情况列表.xlsx
        excelData = xlrd.open_workbook(file_path)  # 打开excel文件
        sheet = excelData.sheet_by_index(0)  # 打开第一个sheet
        rows = sheet.nrows  # 获取总行数
        cols = sheet.ncols  # 获取总列数
        dayList = []
        for row in range(rows):
            rowData = sheet.row(row)  # 获取一行的数据
            targetData = TargetData()
            # 筛选符合条件的数据 ( 仁化县 + 在线率小于100% )
            if rowData[1].value.startswith('广东省/韶关市/仁化县') and '100.0%'.__ne__(rowData[6].value):
                targetData.A = rowData[0].value
                targetData.B = rowData[1].value
                targetData.C = rowData[2].value
                targetData.D = rowData[3].value
                targetData.E = rowData[4].value
                targetData.F = rowData[5].value
                targetData.G = rowData[6].value
                targetData.H = rowData[7].value
                dayList.append(targetData)

        todayData = TodayData()
        todayData.fileName = fileName
        todayData.dayList = dayList
        allData.append(todayData)

# 获取所有出现过的村镇
setData = set()
for dayData in allData:
    # print(dayData.fileName)
    for detail in dayData.dayList:
        # print(detail.__dict__)
        setData.add(detail.B)

cunList = []  # 文件里出现过的所有村镇
for cun in setData:
    cunList.append(cun)
cunList.sort()  # 排序

# ############## 开始写入数据到Excel表格 start ####################
wb = xlwt.Workbook()  # 1) 创建工作簿
ws = wb.add_sheet("CNY")  # 2) 创建工作表

titleStyle = xlwt.XFStyle()  # 初始化样式
#  ---------定义单元格对齐方式---------------
cellAlign = xlwt.Alignment()
cellAlign.horz = 0x02  # 水平居中对齐
cellAlign.vert = 0x01  # 垂直居中对齐

titleStyle.alignment = cellAlign  # 设置单元格对齐方式

# 3) 填充工作表内容
ws.write_merge(0, 1, 0, 5, "按日期对比分析", titleStyle)  # 合并 第1-2行与第1-6列 为一个单元格


#  统计所有天数出现的异常情况信息
index = 5  # 起始写入Excel的位置
for cun in cunList:  # 循环所有的镇街
    for dayData in allData:  # 循环所有的天数对应的表格
        # print(dayData.fileName)
        for detail in dayData.dayList:
            # print(detail.__dict__)
            if cun.__eq__(detail.B):
                # print(detail.__dict__)
                ws.write(index, 0, detail.A)
                ws.write(index, 1, detail.B)
                ws.write(index, 2, detail.C)
                ws.write(index, 3, detail.D)
                ws.write(index, 4, detail.E)
                ws.write(index, 5, detail.F)
                ws.write(index, 6, detail.G)
                ws.write(index, 7, detail.H)
                index = index + 1

# 添加表头信息
ws.write(4, 0, '区划编码')
ws.write(4, 1, '区划名称')
ws.write(4, 2, '是否完成目标')
ws.write(4, 3, '目标完成数')
ws.write(4, 4, '实际完成数')
ws.write(4, 5, '视频在线数')
ws.write(4, 6, '视频在线率')
ws.write(4, 7, '统计日期')


outFileDirectory = 'D:\\bwq-out\\按日期对比差异.xls'
wb.save(outFileDirectory)  # 不支持,xlsx格式
